﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Data;

namespace DataCollectService
{
    public class CommonSql : DbHelperSQL
    {
        #region 对外方法
        /// <summary>
        /// 对外方法--获取数据
        /// </summary>
        /// <param name="userKey"></param>
        /// <param name="objname"></param>
        /// <param name="msg"></param>
        /// <param name="sqlValues"></param>
        /// <returns></returns>
        public static DataSet getDataSetx(string userKey, string objname, ref string msg, params object[] sqlValues)
        {
            //传入的参数
            string[] parmss_name = { "username", "num" };

            //SQL数据类型相关操作
            Dictionary<Type, SqlDbType> tt = getSqlDbType();

            List<SqlParameter> parameters = new List<SqlParameter>();

            for (int i = 0; i < parmss_name.Length; i++)
            {
                parameters.Add(new SqlParameter("@tblName", tt[sqlValues[i].GetType()]));
            }

            SqlParameter[] SqlParameters = parameters.ToArray();

            SqlParameters[0].Value = "";


            return Query("", SqlParameters);
        }
        #endregion
        #region 对内方法
        /// <summary>
        /// 对内方法--获取数据
        /// </summary>
        /// <param name="sqlStr"></param>
        /// <param name="name_value"></param>
        /// <returns></returns>
        public static DataSet _Query(string sqlStr, Dictionary<string, object> name_value)
        {

            //SQL数据类型相关操作
            Dictionary<Type, SqlDbType> tt = getSqlDbType();

            List<SqlParameter> parameters = new List<SqlParameter>();
            foreach (var item in name_value)
            {
                parameters.Add(new SqlParameter("@" + item.Key, tt[item.Value.GetType()]));
            }

            SqlParameter[] SqlParameters = parameters.ToArray();

            int i = 0;
            foreach (var item in name_value)
            {
                SqlParameters[i].Value = item.Value;
                i++;
            }

            return Query(sqlStr, SqlParameters);
        }

        /// <summary>
        /// 对内方法--执行
        /// </summary>
        /// <param name="sqlStr"></param>
        /// <param name="name_value"></param>
        /// <returns></returns>
        public static int _ExecuteSql(string sqlStr,  Dictionary<string, object> name_value)
        {

            //SQL数据类型相关操作
            Dictionary<Type, SqlDbType> tt = getSqlDbType();

            List<SqlParameter> parameters = new List<SqlParameter>();
            foreach (var item in name_value)
            {
                parameters.Add(new SqlParameter("@" + item.Key, tt[item.Value.GetType()]));
            }

            SqlParameter[] SqlParameters = parameters.ToArray();

            int i = 0;
            foreach (var item in name_value)
            {
                SqlParameters[i].Value = item.Value;
                i++;
            }

            return ExecuteSql(sqlStr, SqlParameters);
        }

        #endregion
        /// <summary>
        /// SQL数据类型相关操作
        /// </summary>
        public static Dictionary<string, SqlDbType> getStringToSqlDbType()
        {
            #region C#类型转SQL数据类型集合
            Dictionary<string, SqlDbType> typeMap = new Dictionary<string, SqlDbType>();
            typeMap["nvarchar"] = SqlDbType.NVarChar;
            typeMap["int"] = SqlDbType.Int;
            typeMap["bigint"] = SqlDbType.BigInt;
            typeMap["binary"] = SqlDbType.Binary;
            typeMap["bit"] = SqlDbType.Bit;
            typeMap["char"] = SqlDbType.Char;
            typeMap["datetime"] = SqlDbType.DateTime;
            typeMap["text"] = SqlDbType.Text;
            #endregion
            return typeMap;
        }
        /// <summary>
        /// SQL数据类型相关操作
        /// </summary>
        public static Dictionary<Type, SqlDbType> getSqlDbType()
        {
            #region C#类型转SQL数据类型集合
            Dictionary<Type, SqlDbType> typeMap = new Dictionary<Type, SqlDbType>();
            typeMap[typeof(Int32)] = SqlDbType.Int;
            typeMap[typeof(bool)] = SqlDbType.Bit;
            typeMap[typeof(byte)] = SqlDbType.TinyInt;
            typeMap[typeof(sbyte)] = SqlDbType.TinyInt;
            typeMap[typeof(short)] = SqlDbType.SmallInt;
            typeMap[typeof(ushort)] = SqlDbType.SmallInt;
            typeMap[typeof(uint)] = SqlDbType.Int;
            typeMap[typeof(long)] = SqlDbType.BigInt;
            typeMap[typeof(ulong)] = SqlDbType.BigInt;
            typeMap[typeof(decimal)] = SqlDbType.Decimal;
            typeMap[typeof(float)] = SqlDbType.Real;
            typeMap[typeof(double)] = SqlDbType.Float;
            typeMap[typeof(string)] = SqlDbType.NVarChar;
            typeMap[typeof(DateTime)] = SqlDbType.DateTime;
            typeMap[typeof(Guid)] = SqlDbType.UniqueIdentifier;
            typeMap[typeof(byte[])] = SqlDbType.VarBinary;
            #endregion
            return typeMap;
        }

    }
}